package util;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;

import org.apache.poi.hssf.usermodel.DVConstraint;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataValidation;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.ss.util.Region;

/**
 * 说明：poi对excel操作的辅助类.
 * 
 * @author liuzd
 * @version 1.0
 */
public class PoiExcelUtil {

	private PoiExcelUtil() {
	}

	/**
	 * 操作：给表格里的单元格绑定下拉选择框,并设置内容.<br>
	 * 刘柱栋 2013-4-13
	 * 
	 * @param sheet
	 *            表格
	 * @param selectContents
	 *            下拉框的内容
	 * @param sites
	 *            单元格的位置,四位长度的数组<br>
	 *            sites = [firstRow, lastRow, firstCol, lastCol]
	 * @version 1.0
	 */
	public static void setSelectStyleForSheet(HSSFSheet sheet,
			String[] selectContents, int[] sites) {
		if (sheet == null) {
			return;
		} else if (sites == null) {
			return;
		} else if (selectContents == null) {
			return;
		} else if (sites.length != 4) {
			return;
		} else {
			// // 列绑定下拉框
			CellRangeAddressList regions = new CellRangeAddressList(sites[0],
					sites[1], sites[2], sites[3]);
			DVConstraint constraint = DVConstraint
					.createExplicitListConstraint(selectContents);
			HSSFDataValidation data_validation = new HSSFDataValidation(
					regions, constraint);
			sheet.addValidationData(data_validation);
		}
	}


	private static HSSFWorkbook createHssfWorkbook() {
		HSSFWorkbook workbook = new HSSFWorkbook();

		HSSFSheet sheet = workbook.createSheet("产品平台配置清单导入表");
		sheet.setDefaultColumnWidth(15);
		// 样式：：：居左 加粗 有边框
		HSSFCellStyle style = workbook.createCellStyle();
		style.setAlignment(HSSFCellStyle.ALIGN_LEFT);
		style.setBorderTop(HSSFCellStyle.BORDER_THIN);
		style.setBorderRight(HSSFCellStyle.BORDER_THIN);
		style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		HSSFFont font = workbook.createFont();
		font.setFontHeightInPoints((short) 10);
		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
		style.setFont(font);

		// 样式：：：居中 正常 有边框
		HSSFCellStyle style2 = workbook.createCellStyle();
		style2.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		style2.setBorderTop(HSSFCellStyle.BORDER_THIN);
		style2.setBorderRight(HSSFCellStyle.BORDER_THIN);
		style2.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		style2.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		HSSFFont font2 = workbook.createFont();
		font2.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
		style2.setFont(font2);

		// 样式 :: 字体正常，居左 有边框
		HSSFCellStyle style3 = workbook.createCellStyle();
		style3.setAlignment(HSSFCellStyle.ALIGN_LEFT);
		style3.setBorderTop(HSSFCellStyle.BORDER_THIN);
		style3.setBorderRight(HSSFCellStyle.BORDER_THIN);
		style3.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		style3.setBorderLeft(HSSFCellStyle.BORDER_THIN);
		HSSFFont font3 = workbook.createFont();
		font3.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
		style3.setFont(font3);

		// 样式：：：居左 正常
		HSSFCellStyle style4 = workbook.createCellStyle();
		style4.setAlignment(HSSFCellStyle.ALIGN_LEFT);
		// style2.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index);
		// style2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		HSSFFont font4 = workbook.createFont();
		font4.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
		style4.setFont(font4);
		style4.setWrapText(true);

		// 样式：：：居中 正常
		HSSFCellStyle style5 = workbook.createCellStyle();
		style5.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		// style2.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index);
		// style2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		HSSFFont font5 = workbook.createFont();
		font5.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
		style5.setFont(font4);
		style5.setWrapText(true);

		String[] heads_4 = { "*特性代码 ", "*特性名称", "*特性更改号码", "*特性类别",
				"*特性类别覆盖标记", "*一般特改标记", "*一般特改覆盖标记", "*特性值代码", "特性值技术描述",
				"特性值销售描述", "*特性值更改号码", "*特性值类别", "*特性值类别覆盖标记", "*默认标记",
				"*默认标记覆盖标记", "标配版本约束", "*标配版本约束覆盖标记", "等级可选约束", "*等级可选约束覆盖标记",
				"配置优选约束", "*配置优选约束覆盖标记", "配置可选约束", "*配置可选约束覆盖标记", "需求可选约束",
				"*需求可选约束覆盖标记", "需求优选约束", "*需求优选约束覆盖标记", "销售备注文本",
				"*销售备注文本覆盖标记", "标配版本约束描述", "*标配版本约束描述覆盖标记", "等级约束描述",
				"*等级约束描述覆盖标记	配置可选约束描述", "*配置可选约束描述覆盖标记", "配置优选约束描述",
				"*配置优选约束描述覆盖标记", "需求可选约束描述", "*需求可选约束描述覆盖标记", "需求优选约束描述",
				"*需求优选约束描述覆盖标记", "配置表输出项", "*配置表输出项覆盖标记" };
		// // 设置边框
		int size = heads_4.length;
		for (int i = 5; i < 15; i++) {
			HSSFRow row = sheet.createRow(i);
			for (int j = 0; j < size; j++) {
				HSSFCell cell = row.createCell(j);
				cell.setCellStyle(style3);
			}
		}
		//
		sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 6));
		HSSFRow row_0 = sheet.createRow(0);
		createCellByRow(sheet, row_0, style2, size,
				new String[] { "XXX产品平台配置清单导入表" });
		// //
		HSSFRow row_1 = sheet.createRow(1);
		String[] heads_1 = { "*制表人", "", "制表部门", "", "*制表时间" };
		createCellByRow(sheet, row_1, style2, size, heads_1);
		// //
		HSSFRow row_2 = sheet.createRow(2);
		String[] heads_2 = { "*审核人", "", "审核部门", "", "审核时间" };
		createCellByRow(sheet, row_2, style2, size, heads_2);
		// ///
		HSSFRow row_3 = sheet.createRow(3);
		String[] heads_3 = { "*产品平台代码", "", "*产品平台描述", "", "*更改号码" };
		createCellByRow(sheet, row_3, style, size, heads_3);
		// //
		HSSFRow row_4 = sheet.createRow(4);
		createCellByRow(sheet, row_4, style, size, heads_4);
		// //
		for (int i = 0; i < size; i++) {
			String str = heads_4[i];
			PoiExcelUtil.setColumWidth(sheet, i, str.length() + 1);
		}
		// ////
		HSSFCell cell_4_8 = row_4.getCell(8);
		cell_4_8.setCellStyle(style3);
		HSSFCell cell_4_9 = row_4.getCell(9);
		cell_4_9.setCellStyle(style3);
		for (int i = 15; i < size; i += 2) {
			HSSFCell cell = row_4.getCell(i);
			cell.setCellStyle(style3);
		}
		// 冻结Excel的窗口，边界为数据部分的边界
		sheet.createFreezePane(3, 5);

		// //////
		HSSFSheet sheet2 = workbook.createSheet("填写说明");
		sheet2.setDefaultColumnWidth(10);
		sheet2.addMergedRegion(new CellRangeAddress(0, 0, 1, 6));
		sheet2.addMergedRegion(new CellRangeAddress(1, 10, 1, 6));
		sheet2.addMergedRegion(new CellRangeAddress(11, 11, 1, 6));
		sheet2.addMergedRegion(new CellRangeAddress(12, 18, 1, 6));
		sheet2.addMergedRegion(new CellRangeAddress(19, 19, 1, 6));
		sheet2.addMergedRegion(new CellRangeAddress(20, 23, 1, 6));
		sheet2.addMergedRegion(new CellRangeAddress(0, 0, 8, 14));
		sheet2.addMergedRegion(new CellRangeAddress(1, 27, 8, 14));

		HSSFRow row_2_0 = sheet2.createRow(0);
		HSSFCell cell1 = row_2_0.createCell(1);
		cell1.setCellValue("产品平台相关信息 填写说明");
		cell1.setCellStyle(style5);
		HSSFCell cell2 = row_2_0.createCell(8);
		cell2.setCellValue("特性与特性值相关信息 填写说明");
		cell2.setCellStyle(style5);

		HSSFRow row_2_1 = sheet2.createRow(1);
		HSSFCell cell = row_2_1.createCell(1);
		cell.setCellValue(new HSSFRichTextString("备注:  *为必填字段"
				+ "\n制表人  必填       字符类型    最大10个中文文字"
				+ "\n审核人  必填       字符类型    最大10个中文文字"
				+ "\n制表部门   选填    字符类型    最大128个中文文字"
				+ "\n审核部门   选填    字符类型    最大128个中文文字"
				+ "\n制表时间   必填    年-月-日，XXXX-XX-XX,如2012-06-16"
				+ "\n审核时间   选填    年-月-日，XXXX-XX-XX,如2012-06-16" + "\n"
				+ "\n产品平台代码    必填 字符类型 最大18个字符 "
				+ "\n产品平台描述    必填 文字类型 最大30个中文文字"
				+ "\n更改号码        必填  数字类型 最大16个字符  通过系统生成\n"));
		cell.setCellStyle(style4);

		HSSFCell cell_2_8 = row_2_1.createCell(8);
		cell_2_8.setCellValue(new HSSFRichTextString("备注:  *为必填字段" + "\n"
				+ "\n特性代码          必填 字符类型 最大40个字符"
				+ "\n特性名称          必填 字符类型 最大30个中文文字"
				+ "\n特性更改号码       必填 数字类型 最大16个字符  通过系统生成 "
				+ "\n特性值代码         必填 字符类型 最大40个字符"
				+ "\n特性值技术描述       选填 字符类型 最大128个中文文字"
				+ "\n特性值销售描述       选填 字符类型 最大128个中文文字"
				+ "\n销售备注             选填 字符类型 最大128个中文文字" + "\n\n"
				+ "\n特性值更改号码      必填  数字类型 最大16个字符  通过系统生成"
				+ "\n特性值类别          必填 下拉选择  推荐/可选/备选/储备/淘汰"
				+ "\n特性值类别覆盖标记   必填 下拉选择 是/否" + "\n默认标记            必填 下拉选择 是/否"
				+ "\n默认标记覆盖标记     必填 下拉选择 是/否   "
				+ "\n标配版本约束          选填 字符类型,最大2048个字符,英文占1个字符，中文占2个字符"
				+ "\n标配版本约束覆盖标记  必填 下拉选择 是/否"
				+ "\n等级可选约束           选填 文字类型,最大2048个字符,英文占1个字符，中文占2个字符"
				+ "\n等级可选约束覆盖标记  必填 下拉选择 是/否"
				+ "\n配置可选约束           选填 文字类型,最大2048个字符,英文占1个字符，中文占2个字符"
				+ "\n配置可选约束覆盖标记  必填 下拉选择 是/否"
				+ "\n配置优选约束           选填 文字类型,最大2048个字符,英文占1个字符，中文占2个字符"
				+ "\n配置优选约束覆盖标记  必填 下拉选择 是/否"
				+ "\n需求可选约束           选填 文字类型,最大2048个字符,英文占1个字符，中文占2个字符"
				+ "\n需求可选约束覆盖标记  必填 下拉选择 是/否"
				+ "\n需求优选约束           选填 文字类型,最大2048个字符,英文占1个字符，中文占2个字符"
				+ "\n需求优选约束覆盖标记  必填 下拉选择 是/否"
				+ "\n配置表输出项           选填 下拉选择   输出/不输出"
				+ "\n配置表输出项覆盖标记   必填 下拉选择 是/否\n"));
		cell_2_8.setCellStyle(style4);

		HSSFRow row_2_11 = sheet2.createRow(11);
		HSSFCell cell_2_11_1 = row_2_11.createCell(1);
		cell_2_11_1.setCellValue("配置规则填写说明");
		cell_2_11_1.setCellStyle(style5);

		HSSFRow row_2_12 = sheet2.createRow(12);
		HSSFCell cell_2_12_1 = row_2_12.createCell(1);
		cell_2_12_1.setCellValue(new HSSFRichTextString(
				"AND、OR、IN、NOT IN、＝、<>、（、）等逻辑判断符" + "\n• 逻辑判断符之间用空格分割"
						+ "\n• 产品配置、需求、销售区域代码、客户代码都可以作为逻辑定义元素" + "\n例如："
						+ "\n约束条件：ABS为‘进口WABCO’并且 前桥 为‘东风德纳桥4.5吨’" + "\n"
						+ "\n约束规则：(ABS= 'ABS01') AND (QQ IN('QQ01','QQ02'))"));
		cell_2_12_1.setCellStyle(style4);

		HSSFRow row_2_19 = sheet2.createRow(19);
		HSSFCell cell_2_19_1 = row_2_19.createCell(1);
		cell_2_19_1.setCellValue("文件名填写说明");
		cell_2_19_1.setCellStyle(style5);

		HSSFRow row_2_20 = sheet2.createRow(20);
		HSSFCell cell_2_20_1 = row_2_20.createCell(1);
		cell_2_20_1.setCellValue(new HSSFRichTextString(
				"文件名中的‘XXX’与模板标题中的‘XXX’保持一致；XXX代表产品平台描述"
						+ "\n文件名中的‘制表人’与模板内的‘制表人’保持一致"
						+ "\n文件名中的‘制表时间’与模板内的‘制表时间’保持一致"
						+ "\n文件名的‘制表时间’格式(年月日)：XXXXXXXX  如：20120616"));
		cell_2_20_1.setCellStyle(style4);

		sheet2.setColumnWidth(0, TABLE_UNIT_BASEWIDTH);
		sheet2.setColumnWidth(7, TABLE_UNIT_BASEWIDTH);

		return workbook;
	}

	private static final int TABLE_UNIT_BASEWIDTH = 500;

	private static void setColumWidth(HSSFSheet sheet, int i, int lenght) {
		lenght = lenght * TABLE_UNIT_BASEWIDTH;
		sheet.setColumnWidth(i, lenght);
	}

	private static void createCellByRow(HSSFSheet sheet, HSSFRow row,
			HSSFCellStyle style, int size, String[] heads) {
		int endIndex = heads.length;
		for (int i = 0; i < size; i++) {
			HSSFCell cell = row.createCell(i);
			if (i < endIndex) {
				cell.setCellValue(heads[i]);
			}

			cell.setCellStyle(style);
		}
	}

	/**
	 * 刘柱栋 2013-4-13
	 * 
	 * @param args
	 */
	public static void main(String[] args) throws Exception {

		String path = "D:\\1.xls";
		File file = new File(path);
		if (!file.exists()) {
			return;
		}
		// out the excel file
		HSSFWorkbook workbook_out = new HSSFWorkbook();
		// read the excel file
		InputStream inputStream = new FileInputStream(file);
		HSSFWorkbook workbook = new HSSFWorkbook(inputStream);

		for (int i = 0, sheetSize = workbook.getNumberOfSheets(); i < sheetSize; i++) {
			HSSFSheet sheet = workbook.getSheetAt(i);
			HSSFSheet sheet_out = workbook_out.createSheet();
			for (int j = 0, rowSize = sheet.getPhysicalNumberOfRows(); j < rowSize; j++) {
				HSSFRow row = sheet.getRow(j);
				if (row == null) {
					continue;
				}
				HSSFRow row_out = sheet_out.createRow(j);
				for (int z = 0, cellSize = row.getPhysicalNumberOfCells(); z < cellSize; z++) {
					HSSFCell cell = row.getCell(z);
					if(cell == null){
						continue;
					}
					String value = validate(sheet, j, z);
					if(value == null){
						value = getCellValue(cell);
					}
					HSSFCell cell_out = row_out.createCell(z);
					cell_out.setCellValue(value);
				}
			}
		}

		inputStream.close();
		// out file
		String outpath = "D:\\111out.xls";
		outPutWorkBook(workbook_out, outpath);

	}
	
	/**
	 * 操作：实现Excel文件的输出功能.<br>
	 * 刘柱栋 2013-4-14
	 * 
	 * @param workbook
	 * @param outputFilename
	 *            输出文件的路径+名称
	 * @throws Exception
	 */
	public static void outPutWorkBook(HSSFWorkbook workbook,
			String outputFilename) throws Exception {
		FileOutputStream out = null;
		try {
			out = new FileOutputStream(outputFilename);
			workbook.write(out);
		} catch (Exception e) {
			RuntimeException ex = new IllegalArgumentException(
					"导出Excel时产生异常......");
			ex.initCause(e);
			throw ex;
		} finally {
			try {
				if (out != null) {
					out.close();
				}
			} catch (Exception e) {
				RuntimeException ex = new IllegalArgumentException(
						"关闭输出流时产生异常......");
				ex.initCause(e);
				throw ex;
			} finally {
				out = null;
			}
		}
	}

	/**
	 * 通过单元格获取单元格的值.
	 * 刘柱栋    2013-4-21
	 * @param cell
	 * @return
	 */
	private static String getCellValue(HSSFCell cell) {
		String value = null;
		if (cell == null) {
			return value;
		} else {
			switch (cell.getCellType()) {

			case HSSFCell.CELL_TYPE_FORMULA:
				value = cell.getCellFormula();
				break;

			case HSSFCell.CELL_TYPE_NUMERIC:
				value = cell.getNumericCellValue()+"";
				break;

			case HSSFCell.CELL_TYPE_STRING:
				value =  cell.getStringCellValue();
				break;

			default:
			}
		}
		
		return value;

	}

	/**
	 * 验证单元格是否在合并的单元格中，是返回合并单元格的值。
	 * 刘柱栋    2013-4-21
	 * @param sheet
	 * @param rowindex
	 * @param cellindex
	 * @return
	 */
	private static String validate(HSSFSheet sheet,int rowindex,int cellindex) {
		String value = null;
		int size = sheet.getNumMergedRegions();
		for (int i = 0; i < size; i++) {
			CellRangeAddress c = sheet.getMergedRegion(i);
			if (c.isInRange(rowindex, cellindex)) {
				HSSFRow row = sheet.getRow(c.getFirstRow());
				HSSFCell cell = row.getCell(c.getFirstColumn());
				value = getCellValue(cell);
			}
		}
		return value;
	}
}
